#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
with
tmp1_table1 as 没有就业指导与培训的数据基础
(
select
	num,
	null percent1,
	'培训会' content,
	'就业指导与培训' name,
	df_time as operate_time
from (
	select
		df_time,
		count(1) as num
	from (
		select
			date_format(hold_date,'yyyy-MM') as df_time,
			job_fair_name
		from dws_job_fair
		)a
	group by df_time
	order by df_time desc
	limit 12
	)aa
),


tmp1_table2 as
(
select
	num,
	null percent1,
	'参会人数' content,
	'就业指导与培训' name,
	df_time as operate_time
from (
	select
		df_time,
		sum(admission_sum) as num
	from (
		select
			date_format(hold_date,'yyyy-MM') as df_time,
			admission_sum
		from dws_job_fair
		)a
	group by df_time
	order by df_time desc
	limit 12
	)aa

union all
select
	num,percent1,content,name,operate_time
from tmp1_table1

),


tmp2_table as
(
select
	job_fair_name as content,
	hold_date as operate_time,
	admission_sum as num,
	null percent1,
	'最新就业指导会' name
from dws_job_fair
order by operate_time desc
limit 10
),




tmp3_table1 as
(
select
	num,
	null percent1,
	'培训会' content,
	'就业指导与培训' name,
	df_time as operate_time
from (
	select
		df_time,
		count(1) as num
	from (
		select
			date_format(hold_date,'yyyy-MM') as df_time,
			job_fair_name
		from dws_job_fair
		)a
	group by df_time
	order by df_time desc
	limit 12
	)aa

),

tmp3_table2 as
(
select
	num,
	null percent1,
	'培训会' content,
	'就业指导与培训' name,
	df_time as operate_time
from (
	select
		df_time,
		sum(admission_sum) as num
	from (
		select
			date_format(hold_date,'yyyy-MM') as df_time,
			admission_sum
		from dws_job_fair
		)a
	group by df_time
	order by df_time desc
	limit 12
	)aa

union all
select
	num,percent1,content,name,operate_time
from tmp1_table1
),



tmp4_table as
(
select
	num,
	null percent1,
	key_service_type as content,
	'2+N招聘会' name,
	cast(current_date() as string)  as operate_time
from (
	select
		count(1) as num,
		key_service_type
	from dws_job_fair
	group by key_service_type
	)a

)


insert overwrite table cy_database.ads_big_screen_result_train_recruit
select num,percent1,content,name,operate_time from tmp1_table2

union all
select num,percent1,content,name,operate_time from tmp2_table

union all
select num,percent1,content,name,operate_time from tmp3_table2
union all
select num,percent1,content,name,operate_time from tmp4_table

;
"

$hive -e "$sql"